CREATE DATABASE BVVD
GO
USE BVVD
GO
CREATE TABLE LST_MENUTOP
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MAMNTOP VARCHAR(15) NOT NULL UNIQUE,
	TEN NVARCHAR(100),
	LINK VARCHAR(100),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)
GO
CREATE TABLE LST_MENULEFT
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MAMNLEFT VARCHAR(15) NOT NULL UNIQUE,
	TEN NVARCHAR(100),
	LINK VARCHAR(100),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)
GO
CREATE TABLE LST_LOAIDM
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MALOAIDM VARCHAR(50) NOT NULL UNIQUE,
	TENLOAIDM NVARCHAR(100),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)
GO
CREATE TABLE LST_DANHMUC
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MALOAIDM VARCHAR(50) FOREIGN KEY REFERENCES LST_LOAIDM(MALOAIDM),
	MADM VARCHAR(50) NOT NULL UNIQUE,
	TENDM NVARCHAR(100),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)
GO
CREATE TABLE LST_TINHTP
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MAQUOCGIA VARCHAR(50) FOREIGN KEY REFERENCES LST_DANHMUC(MADM),
	MATINH VARCHAR(50) NOT NULL UNIQUE ,
	TENTINH NVARCHAR(100),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)
GO
CREATE TABLE LST_QUANHUYEN
(
	
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MATINH VARCHAR(50) FOREIGN KEY REFERENCES LST_TINHTP(MATINH),
	MAQH VARCHAR(50),
	TENQH NVARCHAR(100),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)

GO


CREATE TABLE LST_LOPHOC
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MALH VARCHAR(15) NOT NULL UNIQUE,
	TENLH NVARCHAR(100),
	KIEULOPHOC INT,
	THOIGIANHOC INT,
	HOCPHI NUMERIC(10,2),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)

CREATE TABLE LST_MONHOC
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MAMH VARCHAR(15) NOT NULL UNIQUE,
	TENMH NVARCHAR(100) ,
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)

)
GO
CREATE TABLE LST_GIANGVIEN
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MAGV VARCHAR(15)NOT NULL UNIQUE,
	TENGV NVARCHAR(100),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	CHUCVU NVARCHAR(100),
	DONVICT NVARCHAR(100),
	EMAIL VARCHAR(100),
	TEL VARCHAR(50),
	MOBILE VARCHAR(50),
	GHICHU NTEXT,
	GHICHU1 NTEXT,
	GHICHU2 NTEXT,
	GHICHU3 NTEXT
)
GO
CREATE TABLE LST_HOCVIEN
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MAHV VARCHAR(15) NOT NULL UNIQUE,
	HODEM NVARCHAR(50),
	TENHV NVARCHAR(50),
	NGAYSINH DATE,
	GIOITINH NVARCHAR(50),
	LOAIGIAY INT DEFAULT 0,
	SOCMND VARCHAR(50),
	NGAYCAP DATE,
	NOICAP NVARCHAR(50),
	DIACHI NVARCHAR(300),
	HOKHAU NVARCHAR(300),
	QUEQUAN NVARCHAR(100),
	TEL VARCHAR(50),
	MOBILE VARCHAR(50),
	EMAIL VARCHAR(50),
	QUOCTICH NVARCHAR(50),
	DANTOC NVARCHAR(50),
	TONGIAO NVARCHAR(50),
	COQUANCT NVARCHAR(200),
	CHUCVU NVARCHAR(100),
	CHUCDANH NVARCHAR(100),
	TRINHDO NVARCHAR(100),
	CHUYENMON NVARCHAR(100),
	DANGVIEN INT DEFAULT 0,
	NGAYVAODANG DATE,
	DOANVIEN INT DEFAULT 0,
	NGAYDOANVIEN DATE,
	THUONGBINH INT DEFAULT 0,
	CONLIETSY INT DEFAULT 0,
	THAMNIEN INT DEFAULT 0,
	KHENTHUONG NTEXT,
	KYLUAT NTEXT,
	PICTURE IMAGE,
	TRANGTHAI VARCHAR(15) DEFAULT '1',
	GHICHU NTEXT
)
GO
CREATE TABLE LST_NHOMNSD
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MANHOM VARCHAR(15) NOT NULL UNIQUE,
	TENNHOM NVARCHAR(100),
	TRANGTHAI VARCHAR(1) DEFAULT '1',
	GHICHU NVARCHAR(500)

)
GO
CREATE TABLE LST_NSD
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MANSD VARCHAR(15) NOT NULL UNIQUE,
	TENNSD NVARCHAR(100),
	MATKHAU VARCHAR(50),
	CHUCVU VARCHAR(15) NOT NULL UNIQUE,
	EMAIL VARCHAR(50),
	TEL VARCHAR(50),
	MOBILE VARCHAR(50),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NTEXT
)
GO


CREATE TABLE F_NHOM_NSD
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MANHOM VARCHAR(15) FOREIGN KEY REFERENCES LST_NHOMNSD(MANHOM) ,
	MANSD VARCHAR(15) FOREIGN KEY REFERENCES LST_NSD(MANSD),
	NGAYGAN DATE DEFAULT CONVERT(DATE,GETDATE(),101)
)
GO

CREATE TABLE F_DONGHOCPHI
(
	RECID  INT IDENTITY(1,1) PRIMARY KEY,
	MAHV VARCHAR(15) FOREIGN KEY REFERENCES LST_HOCVIEN(MAHV),
	SOTIEN NUMERIC(10,2),
	LOAITIEN VARCHAR(15),
	NGAYDHP DATE DEFAULT CONVERT(DATE,GETDATE(),101),
	LAN INT,
	NGUOITHU VARCHAR(15) FOREIGN KEY REFERENCES LST_NSD(MANSD),
	GHICHU NVARCHAR(500)
)
GO

CREATE TABLE F_LOP_HOCVIEN
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MALH VARCHAR(15) FOREIGN KEY REFERENCES LST_LOPHOC(MALH),
	MAHV VARCHAR(15) FOREIGN KEY REFERENCES LST_HOCVIEN(MAHV),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)
)
GO

CREATE TABLE F_LOP_MONHOC
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MALH VARCHAR(15) FOREIGN KEY REFERENCES LST_LOPHOC(MALH),
	MAMH VARCHAR(15) FOREIGN KEY REFERENCES LST_MONHOC(MAMH),
	TRANGTHAI VARCHAR(10) DEFAULT '1',
	GHICHU NVARCHAR(500)

)
GO

CREATE TABLE F_GIAYNX
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	SOGNX VARCHAR(30) NOT NULL UNIQUE,
	MALH VARCHAR(15) FOREIGN KEY REFERENCES LST_LOPHOC(MALH),
	MAHV VARCHAR(15) FOREIGN KEY REFERENCES LST_HOCVIEN(MAHV),
	NGAYNHAN DATE,
	TRANGTHAI VARCHAR(15),
	GHICHU NTEXT

)
GO

CREATE TABLE F_GIAYCN
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	SOGCN VARCHAR(30) NOT NULL UNIQUE,
	MALH VARCHAR(15) FOREIGN KEY REFERENCES LST_LOPHOC(MALH),
	MAHV VARCHAR(15) FOREIGN KEY REFERENCES LST_HOCVIEN(MAHV),
	NGAYNHAN DATE,
	TRANGTHAI VARCHAR(15),
	GHICHU NTEXT

)
GO

CREATE TABLE F_DIEMTHI
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MALH VARCHAR(15) FOREIGN KEY REFERENCES LST_LOPHOC(MALH),
	MAHV VARCHAR(15) FOREIGN KEY REFERENCES LST_HOCVIEN(MAHV),
	MAMH VARCHAR(15) FOREIGN KEY REFERENCES LST_MONHOC(MAMH),
	NGAYTHI DATE,
	LANTHI INT,
	DIEM NUMERIC(4,2),
	TRANGTHAI VARCHAR(15),
	GHICHU NVARCHAR(500)


)


CREATE TABLE F_CONGHOC
(
	RECID INT IDENTITY(1,1) PRIMARY KEY,
	MALH VARCHAR(15) FOREIGN KEY REFERENCES LST_LOPHOC(MALH),
	MAHV VARCHAR(15) FOREIGN KEY REFERENCES LST_HOCVIEN(MAHV),
	MAMH VARCHAR(15) FOREIGN KEY REFERENCES LST_MONHOC(MAMH),
	NGAYHOC DATE,
	TRANGTHAI VARCHAR(15) DEFAULT '0',
	GHICHU NVARCHAR(500)

)
GO

